package procomponent;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

/**
	 * 读取Excel表格
	 * @throws Exception
	 */
public class ReadExcel {
	static com.PublicSystem sys = com.PublicSystem.getInstance();
	/**
	 * 读取 excel
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	public static String readExcel(File file) throws Exception {
		Workbook workbook = null;
		String tf = "";
		String ex = "0";
		java.util.Date de = new Date();
		java.text.DateFormat dated = new java.text.SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
		String s_time = dated.format(de);
		String sbrq = s_time.substring(0,4) + "-01-01";
		long sTime = 0;
		long eTime = 0;
		long Time = 0;
		sTime = System.currentTimeMillis();
		try {
			workbook = Workbook.getWorkbook(file);
			Sheet sheet = workbook.getSheet(0);
			int rowCount = sheet.getRows();
			int cont = sheet.getColumns();
			Cell cell[] = new Cell[cont];
			String label[] = new String[cont];
			String sql[] = new String[rowCount];
			for (int i = 0; i < rowCount; i++) {
				try {
					for(int j = 0; j < cont; j++) {
						cell[j] = sheet.getCell(j,i);
						label[j] = cell[j].getContents();
						if(label[j].trim() == null || ("").equals(label[j].trim())) {
							//ex="信息填写不全请检查申报内容，所有内容必须填写";
							label[j] = "无数据";
						}
					}
					if(("").equals(label[0].trim()) || null == label[0].trim()) {
						label[0] = "无数据";
					}
					//录入前判断日志是否已被终止
					sql[i] = add_grjf(label[0], label[1], label[2], label[3], label[4], label[5], 
									  label[6], label[7], label[8], label[9]);
				}
				catch (Exception ss) {
					ss.printStackTrace();
					ex = label[2]; 
					break;
				}	
			}
			//计算耗时
			eTime = System.currentTimeMillis();
			Time = eTime - sTime;     
			System.out.println("/n******上载解析完毕,数据量：" + sql.length + "条,共耗时:" + Time + "毫秒******");    
			//数据录入计时
			sTime = System.currentTimeMillis();
			add_grjf_bath(sql);
			eTime = System.currentTimeMillis();
			Time = eTime - sTime;     
			System.out.println("/n******录入数据完毕,数据量：" + sql.length + "条,共耗时:" + Time + "毫秒******");    
			workbook.close();
			return ex;
		} catch (Exception e) {
			throw new Exception("file to import not found!");
		} finally {
			workbook.close();
		}
	}
	/**
	 * 个人缴费基数增加
	 */
	public static String add_grjf (String aac001, String aac999, String aac003, String aac058, String aac147, 
	        String aac006, String aac004, String aac005, String aac161, String aac998) throws Exception {
		String sql=aac001 + ";" + aac999 + ";" + aac003+";" + aac058 + ";" + aac147+";" + aac006 + ";"+aac004 + ";" + 
				   aac005+";" + aac161 + ";" + aac998 + ";";
		return sql;  
	}
	/**
	 * 个人缴费基数增加例子
	 * @param sql
	 * @throws Exception
	 */
	public static void add_grjf_bath (String sql[]) throws Exception {
		int num = 0;
		int count = 0;
		Connection con = sys.getConnection();
		PreparedStatement stmt = con.prepareStatement("insert into ZG_WEB_JFSBB(sbnd,dwm,tbr,xm,sfzh,hkxz,jfjs," +
												      "rylb,scsj,dz,yb,yddh,dh) values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
		try {
			for(int i = 0; i < sql.length; i++) {
				count++;  
				String res[] = sql[i].split(";");
				try{
					num = Integer.parseInt(res[5]);
				} catch (Exception fd) {
					num = 0;
				}
				stmt.setString(2, res[0]);
				stmt.setString(3, res[1]);
				stmt.setString(4, res[2]);
				stmt.setString(5, res[3]);
				stmt.setString(6, res[4]);
				stmt.setInt(7, num);
				stmt.setString(8, res[6]);
				stmt.setString(9, com.util.Component.systime());
				stmt.setString(10, res[7]);
				stmt.setString(11, res[8]);
				stmt.setString(12, res[9]);
				stmt.setString(13, res[10]);
				stmt.addBatch();
				if(count%2000 == 0) {    //批处理再提交 
					stmt.executeBatch();
					con.commit();
					stmt.close();
					con.close();
					con = sys.getConnection();
					stmt = con.prepareStatement("insert into ZG_WEB_JFSBB(sbnd,dwm,tbr,xm,sfzh,hkxz,jfjs," +
											    "rylb,scsj,dz,yb,yddh,dh) values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
				}
			} 
			stmt.executeBatch();
			con.commit();
			stmt.close();
			con.close();
		} catch (Exception d) {	
			con.rollback();
			stmt.close();
			con.close();
			d.printStackTrace();
		} finally {
			if (stmt != null) {  
				stmt.close();  
			}  
			if (con != null && !con.isClosed()) {  
				con.close();  
			}  
		}
	}

	public String readExcelJg(String filename,String dwm) throws Exception {
		Workbook workbook = null;
		String tf ="";
		String ex = "0";
		java.util.Date de = new Date();
		java.text.DateFormat dated = new java.text.SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
		String s_time = dated.format(de);
		String sbrq = s_time.substring(0,4) + "-01-01";
		long sTime = 0;
		long eTime = 0;
		long Time= 0;
		sTime = System.currentTimeMillis();
		try {
			workbook = Workbook.getWorkbook(new File(filename));
			String sb=sys.SelResultStr("select dwm from JG_JFSBB_DW where SHTZH='1' and dwm='"+dwm+"'");
			if(sb == null || sb.equals("")) {
				tf = sys.SelResultStr("select dwm from JG_JFSBB_W where dwm='" + dwm + "'");
				if(tf != null)
				{
					sys.executeSql("delete from JG_JFSBB_W  where dwm='" + dwm + "'");
					sys.executeSql("delete from JG_JFSBB_DW  where dwm='" + dwm + "'");
				}     
				Sheet sheet = workbook.getSheet(0);
				int rowCount = sheet.getRows();
				int cont = sheet.getColumns();
				Cell cell[] = new Cell[cont];
				String label[] = new String[cont];
				String sql[] = new String[rowCount];
				for (int i = 5; i < rowCount; i++)
				{
					try
					{
						for(int j = 0; j < cont; j++)
						{
							cell[j] = sheet.getCell(j,i);
							label[j] = cell[j].getContents();
							if(label[j].trim() == null || ("").equals(label[j].trim())) {
								//ex="信息填写不全请检查申报内容，所有内容必须填写";
								label[j]="0";
							}
						}
						sys.executeSql("insert into JG_JFSBB_W(nd,xh,dwm,tbr,xm,SHBZH,gz,jfjs,gz1,gz2,gz3,gz4,"  +
									   "gz5,gz6,bz,bz1,SHTZH) values (to_date('"+sbrq+"','yyyy-mm-dd'),"+label[0] +
									   ",'" + dwm + "','" + label[1] + "','" + label[2] + "','" + label[3] + "'," + label[4]+"," + label[4] +
									   "," + label[5] + "," + label[6] + "," + label[7] + "," + label[8] + "," + label[9] + "," + label[10] +
									   ",'" + label[11] + "','" + label[12] + "','0')");
					} catch (Exception ss) {
						ss.printStackTrace();
						ex = label[2]; 
						break;
					}
				} 
				sys.executeSql("insert into JG_JFSBB_DW(YMD,dwm,sbrq,SBFS,SBRS,SBJFJS,SHTZH) values " +
						       "(to_date('" + sbrq + "','yyyy-mm-dd'),'" + dwm+"',sysdate,'2',(select count(*) from " +
							   "JG_JFSBB_W where dwm='"+dwm+"'),(select sum(gz) from JG_JFSBB_W where dwm='"+dwm+"'),'0')");
			} else {
				ex = "1";	
			}
			workbook.close();
			return ex;
		} catch (Exception e) {
			workbook.close();
			throw new Exception("file to import not found!");
		} finally {
			workbook.close();
		}
	}
}
